﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Module.Core.Member
{
    public class TreeMemBer
    {
        private double gold = 2000;
        private double silver = 4000;
        private double Dimond = 6000;
        String strSql = ConfigurationManager.ConnectionStrings["localsql1"].ToString();
        public TreeMemBer()
        {
            //
            // TODO: Add constructor logic here 
            //
        }
        
        public Boolean checkExists(String codeMemberTop, String codeMember)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "WITH n(codeMember, codeMemberParent) AS ( SELECT codeMember, codeMemberParent  FROM  TreeMember  WHERE codeMember = '" + codeMemberTop + "'  UNION ALL    SELECT m.codeMember, m.codeMemberParent  FROM TreeMember as m, n    WHERE n.codeMember = m.codeMemberParent) SELECT n.* FROM n where n.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                if (dt.Rows.Count > 0)
                {
                    sqlConn.Close(); 
                    return true;
                }
                else
                {
                    sqlConn.Close();
                    return false;
                }

            }
            catch (Exception ee)
            {
                return false;

            }
            return true;
        }
        public Boolean checkExistsXu(String codeMemberTop, String codeMember)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "WITH n(codeMember, codeMemberParent) AS ( SELECT codeMember, codeMemberParent  FROM  TreeMemberXu  WHERE codeMember = '" + codeMemberTop + "'  UNION ALL    SELECT m.codeMember, m.codeMemberParent  FROM TreeMemberXu as m, n    WHERE n.codeMember = m.codeMemberParent) SELECT n.* FROM n where n.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                if (dt.Rows.Count > 0)
                {
                    sqlConn.Close();
                    return true;
                }
                else
                {
                    sqlConn.Close();
                    return false;
                }

            }
            catch (Exception ee)
            {
                return false;

            }
            return true;
        }

        public String getCodeFromId(String id){
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String codeMember = "";
            String sql = "select codeMember from TreeMember where idMember = '"+id+"'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                codeMember = dt.Rows[0]["codeMember"].ToString().Trim();
            sqlConn.Close();
            return codeMember;
        }
        public String getCodeFromIdXu(String id)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String codeMember = "";
            String sql = "select codeMember from TreeMemberXu where idMember = '" + id + "'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                codeMember = dt.Rows[0]["codeMember"].ToString().Trim();
            sqlConn.Close();
            return codeMember;
        }
        public int getIdFromCode(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            int id = 0;
            String sql = "select idMember from TreeMember where codeMember = '" + codeMember + "'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                id = Int32.Parse(dt.Rows[0]["idMember"].ToString().Trim());
            sqlConn.Close();
            return id;
        }
        public int getIdFromCodeXu(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            int id = 0;
            String sql = "select idMember from TreeMemberXu where codeMember = '" + codeMember + "'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                id = Int32.Parse(dt.Rows[0]["idMember"].ToString().Trim());
            sqlConn.Close();
            return id;
        }
        public DataTable getTreeFromPoint(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = " WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro  FROM  TreeMember  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.CodeMemberIntro FROM  n as a, TreeMember as m     WHERE a.idMember = m.codeMemberParent) SELECT n.*,x.CreatedOn,x.FullName , (select sum(price) from UpgradeAccount u where u.idMember = n.codeMember) as  price FROM n , member x where x.CodeMember = n.codeMember order by n.layer asc";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getTreeFromPointXu(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro  FROM  TreeMemberXu  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.CodeMemberIntro FROM  n as a, TreeMemberXu as m     WHERE a.idMember = m.codeMemberParent) SELECT n.*,x.CreatedOn,x.FullName FROM n , member x where x.CodeMember = n.codeMember order by n.layer asc ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }

        public DataTable getTreeFromPoint(String codeMember, int layerCount)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            int layer = 0;
            DataTable dt = getMemberFromCode(codeMember);
            if (dt.Rows.Count > 0)
                layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
            layer += layer + layerCount;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro  FROM  TreeMember  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.CodeMemberIntro FROM  n as a, TreeMember as m     WHERE a.idMember = m.codeMemberParent) SELECT n.*,x.CreatedOn,x.FullName , (select sum(price) from UpgradeAccount u where u.idMember = n.codeMember) as  price FROM n , member x where x.CodeMember = n.codeMember and n.layer < '"+layer+"' order by n.layer asc ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getTreeFromPointXu(String codeMember, int layerCount)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            int layer = 0;
            DataTable dt = getMemberFromCodeXu(codeMember);
            if (dt.Rows.Count > 0)
                layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
            layer += layer + layerCount;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro  FROM  TreeMemberXu  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.CodeMemberIntro FROM  n as a, TreeMemberXu as m     WHERE a.idMember = m.codeMemberParent) SELECT n.*,x.CreatedOn,x.FullName , (select sum(price) from UpgradeAccount u where u.idMember = n.codeMember) as  price FROM n , member x where x.CodeMember = n.codeMember and n.layer < '" + layer + "' order by n.layer asc ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        // lay nhanh du lieu (ko bao gom nut dinh) 
        public DataTable getBranchTree(String codeMember, Boolean left)
        {
            String leftCode = "";
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            dt = getMemberFromCode(codeMember);
            if (dt.Rows.Count > 0)
            {
                if (left)
                    leftCode = dt.Rows[0]["LeftCode"].ToString().Trim();
                else
                    leftCode = dt.Rows[0]["RigthCode"].ToString().Trim();
            }
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,CodeMemberIntro  FROM  TreeMember  WHERE codeMember = '" + leftCode + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.CodeMemberIntro FROM  n as a, TreeMember as m     WHERE a.idMember = m.codeMemberParent) SELECT n.*,x.CreatedOn,x.FullName , (select sum(price) from UpgradeAccount u where u.idMember = n.codeMember) as  price FROM n , member x where x.CodeMember = n.codeMember order by n.layer asc";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }

        public DataTable commissionCalcu(String rcodeMember,int week)
        {
            String leftCode = "";
            String rigthcode = "";
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            dt = getMemberFromCode(rcodeMember);
            if (dt.Rows.Count > 0)
            {
                leftCode = dt.Rows[0]["leftCode"].ToString().Trim();
                rigthcode = dt.Rows[0]["rigthcode"].ToString().Trim();
                String sql = "delete from CommisionGold where week = '" + week + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode ,CodeMemberIntro, flag )             AS (SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,CodeMemberIntro ,' 1'  flag            FROM  TreeMember  WHERE idMember = '" + leftCode + "'              UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode ,m.CodeMemberIntro ,' 1'  flag                  FROM n as a,  TreeMember as m    WHERE  a.idMember = m.codeMemberParent)  insert into CommisionGold (idmember,codeMember, codeMemberParent,leftcode,rigthcode ,CodeMemberIntro, flag, week,BeginDay,EndDay,CreatedOn,status,Price)SELECT n.idmember,'" + rcodeMember + "', n.codeMemberParent,n.leftcode,n.rigthcode ,n.CodeMemberIntro, n.flag,c.week,c.BeginDay,c.EndDay,k.CreatedOn,k.status,k.Price  FROM n , UpgradeAccount k ,calendar c where  n.codeMember = k.idMember and k.CreatedOn between c.BeginDay and c.EndDay  and c.week = '" + week + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode ,CodeMemberIntro, flag )             AS (SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,CodeMemberIntro ,' 0'  flag            FROM  TreeMember  WHERE idMember = '" + rigthcode + "'            UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode ,m.CodeMemberIntro ,' 0'  flag                  FROM n as a,  TreeMember as m    WHERE  a.idMember = m.codeMemberParent)  insert into CommisionGold (idmember,codeMember, codeMemberParent,leftcode,rigthcode ,CodeMemberIntro, flag, week,BeginDay,EndDay,CreatedOn,status,Price)SELECT n.idmember,'" + rcodeMember + "', n.codeMemberParent,n.leftcode,n.rigthcode ,n.CodeMemberIntro, n.flag,c.week,c.BeginDay,c.EndDay,k.CreatedOn,k.status,k.Price  FROM n , UpgradeAccount k ,calendar c where  n.codeMember = k.idMember and k.CreatedOn between c.BeginDay and c.EndDay  and c.week = '" + week + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                storecommissionGold(rcodeMember,week);
                storecommissionGoldId(rcodeMember,week);
            }
            sqlConn.Close();
            return dt;
        }
        public DataTable getCommission(String rCodeMember,String rWeek)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select  a.id,CodeMember,DealXuLevel,DealMaTrix,GoldLevel,GoldMaTrix,GoldCycle,GoldTitle,a.Year,a.Month,GoldUpline,a.week,Gold,DealXu,GoldEquality,LeftExcessGold,LeftGold ,RightGold ,type,GoldId,LeftGoldId,RightGoldId,RightExcessGold,LeftExcessId,RightExcessId,levelMaTrixId ,(convert(varchar,BeginDay,103)+'---'+convert(varchar,EndDay,103)) time from Commission a , calendar b where a.codeMember = '" + rCodeMember + "' and a.week = '" + rWeek + "'  and a.week = b.week  ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getCommission(String rCodeMember, String rWeek,int countWeek)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select top(" + countWeek + ") a.id,CodeMember,DealXuLevel,DealMaTrix,GoldLevel,GoldMaTrix,GoldCycle,GoldTitle,a.Year,a.Month,GoldUpline,a.week,Gold,DealXu,GoldEquality,LeftExcessGold,LeftGold ,RightGold ,type,GoldId,LeftGoldId,RightGoldId,RightExcessGold,LeftExcessId,RightExcessId,levelMaTrixId ,(convert(varchar,BeginDay,103)+'---'+convert(varchar,EndDay,103)) time from Commission a , calendar b where a.codeMember = '" + rCodeMember + "' and a.week <= '" + rWeek + "'  and a.week = b.week order by a.week desc ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getCommission(String rCodeMember)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select  a.id,CodeMember,DealXuLevel,DealMaTrix,GoldLevel,GoldMaTrix,GoldCycle,GoldTitle,a.Year,a.Month,GoldUpline,a.week,Gold,DealXu,GoldEquality,LeftExcessGold,LeftGold,RightGold ,type,GoldId,LeftGoldId,RightGoldId,RightExcessGold ,LeftExcessId,RightExcessId,levelMaTrixId,(convert(varchar,BeginDay,103)+'---'+convert(varchar,EndDay,103)) time from Commission a , calendar b where codeMember = '" + rCodeMember + "' and a.week = b.week ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }

        public void storecommissionGold(String rCodeMember,int rWeek)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            int week = rWeek;
            DataTable dtGold = getSumGold(rCodeMember, week);

            if(dtGold.Rows.Count>0){
                Double left = 0;
                Double right = 0;
                Double commission = 0;
                Double leftExcessGold = 0;
                Double rightExcessGold = 0;

                if (dtGold.Rows[0]["leftGold"].ToString().Trim() != "")
                    left = Double.Parse(dtGold.Rows[0]["leftGold"].ToString().Trim());
                if (dtGold.Rows[0]["rightGold"].ToString().Trim() != "")
                    right = Double.Parse(dtGold.Rows[0]["rightGold"].ToString().Trim());

                //lay du tuan truoc
                DataTable dt = getCommission(rCodeMember, (week - 1).ToString());
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["leftExcessGold"].ToString().Trim() != "")
                        leftExcessGold = Double.Parse(dt.Rows[0]["leftExcessGold"].ToString().Trim());
                    if (dt.Rows[0]["RightExcessGold"].ToString().Trim() != "")
                        rightExcessGold = Double.Parse(dt.Rows[0]["RightExcessGold"].ToString().Trim());
                }
                
                if (left+leftExcessGold > right+rightExcessGold)
                {
                    commission = (right + rightExcessGold) * 0.1;
                    leftExcessGold =  Math.Abs(left+leftExcessGold -right-rightExcessGold);
                }
                else
                {
                    rightExcessGold = Math.Abs(left+leftExcessGold -right-rightExcessGold);
                    commission = (left + leftExcessGold) * 0.1;
                }

                String sql = "";
                if (getCommission(rCodeMember, week.ToString()).Rows.Count > 0)
                {
                    sql = "update Commission set LeftGold = '" + left + "',RightGold = '" + right + "',LeftExcessGold = '" + leftExcessGold + "',RightExcessGold = '" + rightExcessGold + "',GoldMaTrix = '" + commission + "' where codeMember = '" + rCodeMember + "' and  week = '" + week + "'  ";
                }
                else{
                    sql = "insert into Commission(codeMember,LeftGold,RightGold,GoldMaTrix,RightExcessGold,LeftExcessGold,week) values('" + rCodeMember + "','" + left + "','" + right + "','" + commission + "','" + rightExcessGold + "','" + leftExcessGold + "','" + week + "')";
                }
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            sqlConn.Close();
        }
        public DataTable getListCalendar()
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select a.*, (convert(varchar,BeginDay,103)+'-->'+convert(varchar,EndDay,103)) time from calendar a order by week";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getListCalendar(int fromWeek,int countWeek)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select top("+countWeek+") a.*, (convert(varchar,BeginDay,103)+'-->'+convert(varchar,EndDay,103)) time from calendar a where week <= "+fromWeek+" and week > "+(fromWeek-countWeek)+" order by week";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public void storecommissionGoldId(String rCodeMember, int rWeek)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dtId = getSumIdGold(rCodeMember);
            for (int i = 0; i < dtId.Rows.Count; i++)
            {
                Double left = 0;
                Double right = 0;
                Double commission = 0;
                Double leftExcessId = 0;
                Double rightExcessId = 0;
                Double lastLeftExcessId = 0;
                Double lastrightExcessId = 0;
                Boolean forLeft = true;
                int level = 0;
                Double tg = 0;
                if (dtId.Rows[i]["leftId"].ToString().Trim() != "")
                    left = Double.Parse(dtId.Rows[i]["leftId"].ToString().Trim());
                if (dtId.Rows[i]["rightId"].ToString().Trim() != "")
                    right = Double.Parse(dtId.Rows[i]["rightId"].ToString().Trim());

                //lay du tuan truoc
                DataTable dt = getCommission(rCodeMember, (rWeek - 1).ToString());
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[i]["levelMaTrixId"].ToString().Trim() != "")
                        level = Int32.Parse(dt.Rows[i]["levelMaTrixId"].ToString().Trim());
                    if (dt.Rows[0]["leftExcessId"].ToString().Trim() != "")
                        lastLeftExcessId = Double.Parse(dt.Rows[0]["leftExcessId"].ToString().Trim());
                    if (dt.Rows[0]["RightExcessId"].ToString().Trim() != "")
                        lastrightExcessId = Double.Parse(dt.Rows[0]["RightExcessId"].ToString().Trim());
                }

                if (left + lastLeftExcessId > right + lastrightExcessId)
                {
                    tg = right + lastrightExcessId;
                    forLeft = false;
                }
                else
                {
                    tg = left + lastLeftExcessId;
                    forLeft = true;
                }

                Double[] arr1 = new Double[8]{20,49,99,160,450,1500,5000,12000};
                Double[] arr2 = new Double[8] { 5000, 1300, 30000, 60000, 150000, 300000, 1000000, 5000000 };
                leftExcessId = left + lastLeftExcessId;
                rightExcessId = right + lastrightExcessId;
                for (int j = level; j < arr1.Length - 1; j++)
                {
                    if (tg >= arr1[j] && tg < arr1[j+1])
                    {
                        commission = arr2[j];
                        if (forLeft)
                            leftExcessId = tg - arr1[j];
                        else
                            rightExcessId = tg - arr1[j];
                        level = j+1;
                    }
                }
               
                String sql = "";
                String week = dtId.Rows[i]["week"].ToString().Trim();
                if (getCommission(rCodeMember, week).Rows.Count > 0)
                {
                    sql = "update Commission set LeftGoldId = '" + left + "',RightGoldId = '" + right + "',type = 1 ,GoldId = '" + commission + "',LeftExcessId = '" + leftExcessId + "',RightExcessId = '" + rightExcessId + "',levelMaTrixId = '"+level+"' where codeMember = '" + rCodeMember + "' and  week = '" + week + "'  ";
                }
                else
                {
                    sql = "insert into Commission(codeMember,LeftGoldId,RightGoldId,GoldId,type,week,LeftExcessId ,RightExcessId ,levelMaTrixId ) values('" + rCodeMember + "','" + left + "','" + right + "','" + commission + "',1,'" + week + "','" + leftExcessId + "','" + rightExcessId + "','" + level + "')";
                }
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            sqlConn.Close();
        }

        public DataTable getSumGold(String rcodeMember,int week)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select distinct week,(convert(varchar, beginday, 103)+'--'+convert(varchar, EndDay, 103) ) time, (select sum(price) from CommisionGold a where flag = 1 and a.week = k.week and a.codeMember = k.codeMember ) as leftGold , (select sum(price) from CommisionGold b where flag = 0 and b.week = k.week and b.codeMember = k.codeMember)  as  rightGold  from CommisionGold k  where k.codeMember = '" + rcodeMember + "' and k.week = '"+week+"'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getSumIdGold(String rcodeMember)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            int week = getWeek();
            String sql = "select top(1) (select count(distinct idMember) sumId  from CommisionGold a where  k.codeMember = a.codeMember and flag = 1) as  leftId,(select count(distinct idMember) sumId  from CommisionGold b where  k.codeMember =b.codeMember  and flag = 0)  as  rightId , '" + week + "' as week from CommisionGold k where k.codeMember = '" + rcodeMember + "'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }

        public int getWeek()
        {
            int week =0;
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);

            DateTime time = System.DateTime.Today;
            String sql = "select * from calendar where beginDay <= '" + time + "' and EndDay >='"+time+"' ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            week = Int32.Parse(dt.Rows[0]["Week"].ToString().Trim());

            sqlConn.Close();
            return week;
        }
        public DataTable getSumXu(String rcodeMember)
        {
            DataTable dt = null;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "select distinct week,(convert(varchar, beginday, 103)+'--'+convert(varchar, EndDay, 103) ) time, (select sum(price) from CommisionXu a where flag = 1 and a.week = k.week and a.codeMember = k.codeMember ) as leftXu , (select sum(price) from CommisionGold b where flag = 0 and b.week = k.week and b.codeMember = k.codeMember)  as  rightXu  from CommisionXu k  where k.codeMember = '" + rcodeMember + "'";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        
        public int maxLayer(String codeMember)
        {
            int maxLayer = 0;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMember  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer FROM n as a,   TreeMember as m    WHERE a.idMember = m.codeMemberParent)  SELECT max(layer) max FROM n ";
            DataTable dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                maxLayer = Int32.Parse(dt.Rows[0]["max"].ToString());
            sqlConn.Close();
            return maxLayer;
        }
        public int maxLayerXu(String codeMember)
        {
            int maxLayer = 0;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMemberXu  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer FROM n as a,   TreeMemberXu as m    WHERE a.idMember = m.codeMemberParent)  SELECT max(layer) max FROM n ";
            DataTable dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                maxLayer = Int32.Parse(dt.Rows[0]["max"].ToString());
            sqlConn.Close();
            return maxLayer;
        }

        public int upChangeLeve(String codeMember)
        {
            int level = 0;
            DataTable dt = getMemberFromCode(codeMember);


            return level;
        }

        public DataTable getMemberLast(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMember  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer FROM n as a,    TreeMember as m    WHERE a.idMember = m.codeMemberParent) SELECT top(1) n.* FROM n order by  n.Layer  desc";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }
        public DataTable getMemberLastXu(String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMemberXu  WHERE codeMember = '" + codeMember + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer FROM n as a,    TreeMemberXu as m    WHERE a.idMember = m.codeMemberParent) SELECT top(1) n.* FROM n order by  n.Layer  desc";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            sqlConn.Close();
            return dt;
        }

        public int getSumId(String idMember)
        {
            int sumId = 0;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMember  WHERE idMember = '" + idMember + "'           UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer    FROM n as a, TreeMember as m   WHERE a.idMember = m.codeMemberParent) SELECT count(1) sumId FROM n";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                sumId = Int32.Parse(dt.Rows[0]["sumId"].ToString().Trim());
            sqlConn.Close();
            return sumId;
        }
        public int getSumIdXu(String idMember)
        {
            int sumId = 0;
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = null;
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMemberXu  WHERE idMember = '" + idMember + "'           UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer    FROM n as a, TreeMemberXu as m   WHERE a.idMember = m.codeMemberParent) SELECT count(1) sumId FROM n";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count > 0)
                sumId = Int32.Parse(dt.Rows[0]["sumId"].ToString().Trim());
            sqlConn.Close();
            return sumId;
        }

        public String findParentIdXu(String idMemberParent)
        {
            try
            {
                String parentId = "";
                String sqlFind = "";
                string left = "";
                string rigth = "";
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                dt = getMemberFromIdXu(idMemberParent);
                parentId = idMemberParent;
                if (dt.Rows.Count == 0)
                {
                    dt = DBUtilEx.getDataFromSQLServer(sqlConn, "select  idMember from TreeMemberXu a where layer = 1");
                    if (dt.Rows.Count > 0)
                        parentId = dt.Rows[0]["idMember"].ToString().Trim();

                    DataTable dt1 = getMemberFromIdXu(parentId);
                    if (dt1.Rows.Count > 0)
                    {
                        left = dt1.Rows[0]["LeftCode"].ToString().Trim();
                        rigth = dt1.Rows[0]["RigthCode"].ToString().Trim();
                    }
                }
                else
                {
                    left = dt.Rows[0]["LeftCode"].ToString().Trim();
                    rigth = dt.Rows[0]["RigthCode"].ToString().Trim();
                }
                if (left == "" || rigth == "")
                {
                    sqlConn.Close();
                    return parentId;
                }
                else
                {
                    int idLeft = 0;
                    int idRigth = 0;
                    String memberWeek = "";
                    idLeft = getSumIdXu(left);
                    idRigth = getSumIdXu(rigth);
                    if (idLeft <= idRigth)
                        memberWeek = left;
                    else
                        memberWeek = rigth;

                    dt = getMemberFromIdXu(memberWeek);
                    if (dt.Rows.Count == 0)
                    {
                        sqlConn.Close();
                        return memberWeek;
                    }

                    sqlFind = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMemberXu  WHERE idMember = '" + memberWeek + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer     FROM n as a, TreeMember as m   WHERE a.leftCode = m.idMember)  SELECT n.idMember  FROM n  order by n.layer desc ";
                    dt = DBUtilEx.getDataFromSQLServer(sqlConn, sqlFind);
                    if (dt.Rows.Count > 0)
                    {
                        parentId = dt.Rows[0]["idMember"].ToString().Trim();
                    }
                    else
                    {
                        parentId = memberWeek;
                    }
                }

                sqlConn.Close();
                return parentId.Trim();

            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }


        }
        public String findParentId(String idMemberParent)
        {
            try
            {
                String parentId = "";
                String sqlFind = "";
                string left = "";
                string rigth = "";
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                dt = getMemberFromId(idMemberParent);
                parentId = idMemberParent;
                if (dt.Rows.Count == 0)
                {
                    dt = DBUtilEx.getDataFromSQLServer(sqlConn, "select  idMember from TreeMember a where layer = 1");
                    if (dt.Rows.Count > 0)
                        parentId = dt.Rows[0]["idMember"].ToString().Trim();

                    DataTable dt1 = getMemberFromId(parentId);
                    if (dt1.Rows.Count > 0)
                    {
                        left = dt1.Rows[0]["LeftCode"].ToString().Trim();
                        rigth = dt1.Rows[0]["RigthCode"].ToString().Trim();
                    }
                }
                else
                {
                    left = dt.Rows[0]["LeftCode"].ToString().Trim();
                    rigth = dt.Rows[0]["RigthCode"].ToString().Trim();
                }
                if (left == "" || rigth == "")
                {
                    sqlConn.Close();
                    return parentId;
                }
                else
                {
                    int idLeft = 0;
                    int idRigth = 0;
                    String memberWeek = "";
                    idLeft = getSumId(left);
                    idRigth = getSumId(rigth);
                    if (idLeft <= idRigth)
                        memberWeek = left;
                    else
                        memberWeek = rigth;

                    dt = getMemberFromId(memberWeek);
                    if (dt.Rows.Count == 0)
                    {
                        sqlConn.Close();
                        return memberWeek;
                    }

                    sqlFind = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer FROM  TreeMember  WHERE idMember = '" + memberWeek + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer     FROM n as a, TreeMember as m    WHERE a.leftCode = m.idMember)  SELECT n.idMember  FROM n  order by n.layer desc ";
                    dt = DBUtilEx.getDataFromSQLServer(sqlConn, sqlFind);
                    if (dt.Rows.Count > 0)
                    {
                        parentId = dt.Rows[0]["idMember"].ToString().Trim();
                    }
                    else
                    {
                        parentId = memberWeek;
                    }
                }

                sqlConn.Close();
                return parentId.Trim();

            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }

        }

        public DataTable getMemberFromCode(String codeMember)
        {
            try
            {
                DataTable dt = null;
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "select a.*,b.CreatedOn,b.FullName from TreeMember a ,member b where a.codeMember = b.CodeMember and  a.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public DataTable getMemberFromId(String Id)
        {
            try
            {
                DataTable dt = null;
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String codeMember = getCodeFromId(Id.ToString());
                String sql = "select a.*,b.CreatedOn,b.FullName from TreeMember a ,member b where a.codeMember = b.CodeMember and  a.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public DataTable getMemberFromIdXu(String Id)
        {
            try
            {
                DataTable dt = null;
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String codeMember = getCodeFromIdXu(Id);
                String sql = "select a.*,b.CreatedOn,b.FullName from TreeMemberXu a ,member b where a.codeMember = b.CodeMember and  a.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public DataTable getMemberFromCodeXu(String codeMember)
        {
            try
            {
                DataTable dt = null;
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "select a.*,b.CreatedOn,b.FullName from TreeMemberXu a ,member b where a.codeMember = b.CodeMember and  a.codeMember = '" + codeMember + "'";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public DataTable getListMemBer()
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "select a.*,b.FullName,b.CreatedOn ,(select sum(price) from UpgradeAccount u where u.idMember = a.codeMember)  price  from TreeMember a ,member b where a.CodeMember = b.CodeMember order by layer,a.IdMember asc";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public DataTable getListMemBerXu()
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "select a.*,b.FullName,b.CreatedOn  from TreeMemberXu a ,member b where a.CodeMember = b.CodeMember order by layer,a.IdMember asc";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public DataTable getListMemBer(int startId, int pageSize)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "select top(" + pageSize + ") a.*,b.CreatedOn,b.FullName  from TreeMember a , Member b where b.CodeMember = a.codeMember and a.IdMember >= '" + startId + "' order by a.layer,a.idMember asc";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public DataTable getListMemBerXu(int startId, int pageSize)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                String sql = "select top(" + pageSize + ") a.*,b.CreatedOn,b.FullName  from TreeMemberXu a , Member b where b.CodeMember = a.codeMember and a.IdMember >= '" + startId + "' order by a.layer,a.idMember asc";
                dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public Boolean updateLeftRigth(String codeMember, String idMemberParent)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "";
                String leftCode = "";
                String rigthCode = "";
                String idMember = getIdFromCode(codeMember).ToString();
                DataTable dtParent = getMemberFromId(idMemberParent);
                if (dtParent.Rows.Count > 0)
                {
                    leftCode = dtParent.Rows[0]["LeftCode"].ToString().Trim();
                    rigthCode = dtParent.Rows[0]["RigthCode"].ToString().Trim();
                }

                if (leftCode == "")
                {
                    rigthCode = "";
                    leftCode = idMember;
                }
                else if (rigthCode == "")
                {
                    rigthCode = idMember;
                }
                sql = "update TreeMember  set leftcode = '" + leftCode + "', RigthCode = '" + rigthCode + "' where idMember = '" + idMemberParent + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public Boolean updateLeftRigthXu(String codeMember, String idMemberParent)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "";
                String leftCode = "";
                String rigthCode = "";
                String idMember = getIdFromCodeXu(codeMember).ToString();
                DataTable dtParent = getMemberFromIdXu(idMemberParent);
                if (dtParent.Rows.Count > 0)
                {
                    leftCode = dtParent.Rows[0]["LeftCode"].ToString().Trim();
                    rigthCode = dtParent.Rows[0]["RigthCode"].ToString().Trim();
                }
                if (leftCode == "")
                {
                    rigthCode = "";
                    leftCode = idMember;
                }
                else if (rigthCode == "")
                {
                    rigthCode = idMember;
                }
                sql = "update TreeMemberXu  set leftcode = '" + leftCode + "', RigthCode = '" + rigthCode + "' where idMember = '" + idMemberParent + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public Boolean setFreeMem(String idMember, Boolean forLeft)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = getListMemBer();
                if (dt.Rows.Count > 1)
                {
                    String sql = "update TreeMember  set freemember = 1,forLeft = '" + forLeft + "' where idMember = '" + idMember + "' ";
                    DBUtilEx.executeNoneQuery(sqlConn, sql);
                }
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }
        public Boolean setFreeMemXu(String codeMember, Boolean forLeft)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = getListMemBer();
                if (dt.Rows.Count > 1)
                {
                    String sql = "update TreeMemberXu  set freemember = 1,forLeft = '" + forLeft + "' where codeMember = '" + codeMember + "' ";
                    DBUtilEx.executeNoneQuery(sqlConn, sql);
                }
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public Boolean deleteMember()
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "";
            sql = "delete from TreeMember ";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sql = "delete from Member ";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sql = "delete from Commission ";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sql = "delete from UpgradeAccount ";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sql = "delete from TreeMemberXu ";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sqlConn.Close();
            return true;
        }
        public Boolean checkUpDate(String codeMember)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "select * from TreeMember where codeMember = '" + codeMember+"' and upgrade = 'false'  ";
                DataTable  dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                sqlConn.Close();
                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

    
        public Boolean deleteMember(String codeMember)
        {
            try
            {
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                String sql = "";
                sql = "select * from TreeMember a where exists (select 1 from TreeMember b where b.codeMemberParent = a.codeMember ) and codeMember = '" + codeMember + "'";
                DataTable dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
                if (dt.Rows.Count > 0)
                {
                    sqlConn.Close();
                    return false;
                }
                sql = "delete from TreeMember where codeMember = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "delete from TreeMemberXu where codeMember = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "delete from Member where CodeMember = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "delete from Commission where CodeMember = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "delete from UpgradeAccount where IdMember = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "update  TreeMember  set leftCode = null where leftCode = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "update  TreeMember  set RigthCode = null where RigthCode = '" + codeMember + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "update  TreeMember  set leftCode = RigthCode where leftCode is null and RigthCode is not null";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));
            }
        }

        public int upgradeMember(String codeMemberUp)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            if (!checkUpDate(codeMemberUp))
            {
                sqlConn.Close();
                return 0;
            }
            if (codeMemberUp == "12967")
            {
                string xx = "";
            }
            int layerUp = 0;
            int layerDown = 0;
            DataTable dtUp = getMemberFromCode(codeMemberUp);
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,codeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer ,upgrade,codeMemberIntro FROM  TreeMember  WHERE codeMember = '" + codeMemberUp + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.codeMemberIntro  FROM n as a,   TreeMember as m  WHERE m.idMember = a.codeMemberParent ) SELECT top(1) n.* FROM n where upgrade = 0 and codeMember <> '" + codeMemberUp + "' order by  layer asc ";
            DataTable dtDown = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dtDown.Rows.Count > 0)
            {
                //ma nut len
                String idUp = dtUp.Rows[0]["idMember"].ToString().Trim();
                String leftCodeUp = dtUp.Rows[0]["leftCode"].ToString().Trim();
                String rigthCodeUp = dtUp.Rows[0]["rigthCode"].ToString().Trim();
                String codeMemberIntroUp = dtUp.Rows[0]["codeMemberIntro"].ToString().Trim();
                String parentCodeUp = dtUp.Rows[0]["CodeMemberParent"].ToString().Trim();
                layerUp = Int32.Parse(dtUp.Rows[0]["layer"].ToString().Trim());

                //ma nut ha
                String idDown = dtDown.Rows[0]["idMember"].ToString().Trim();
                String leftCodeDown = dtDown.Rows[0]["leftCode"].ToString().Trim();
                String rigthCodeDown = dtDown.Rows[0]["rigthCode"].ToString().Trim();
                String codeMemberIntroDown = dtDown.Rows[0]["codeMemberIntro"].ToString().Trim();
                String parentCodeDown = dtDown.Rows[0]["CodeMemberParent"].ToString().Trim();
                String codeMemberDown = dtDown.Rows[0]["codeMember"].ToString().Trim();
                layerDown = Int32.Parse(dtDown.Rows[0]["layer"].ToString().Trim());

                sql = "update TreeMember set   codeMember = '" + codeMemberUp + "',codeMemberIntro = '" + codeMemberIntroUp + "' , upgrade = 'true'   where idMember = '" + idDown + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "update TreeMember set   codeMember = '" + codeMemberDown + "' ,codeMemberIntro = '" + codeMemberIntroDown + "'  where idMember = '" + idUp + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            else
            {
                sql = "update TreeMember set upgrade = 'true' where codeMember = '" + codeMemberUp + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            sqlConn.Close();
            return layerUp;
        }

        public int upgradeMemberXu(String codeMemberUp)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            if (!checkUpDate(codeMemberUp))
            {
                sqlConn.Close();
                return 0;
            }

            int layerUp = 0;
            int layerDown = 0;
            DataTable dtUp = getMemberFromCodeXu(codeMemberUp);
            String sql = "WITH n(idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer,upgrade,codeMemberIntro) AS ( SELECT idmember,codeMember, codeMemberParent,leftcode,rigthcode,layer ,upgrade,codeMemberIntro FROM  TreeMemberXu  WHERE codeMember = '" + codeMemberUp + "'  UNION ALL    SELECT m.idmember,m.codeMember, m.codeMemberParent,m.leftcode,m.rigthcode,m.layer ,m.upgrade ,m.codeMemberIntro  FROM n as a,   TreeMemberXu as m  WHERE m.idMember = a.codeMemberParent ) SELECT top(1) n.* FROM n where upgrade = 0 and codeMember <> '" + codeMemberUp + "' order by  layer asc ";
            DataTable dtDown = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dtDown.Rows.Count > 0)
            {
                //ma nut len
                String idUp = dtUp.Rows[0]["idMember"].ToString().Trim();
                String leftCodeUp = dtUp.Rows[0]["leftCode"].ToString().Trim();
                String rigthCodeUp = dtUp.Rows[0]["rigthCode"].ToString().Trim();
                String codeMemberIntroUp = dtUp.Rows[0]["codeMemberIntro"].ToString().Trim();
                String parentCodeUp = dtUp.Rows[0]["CodeMemberParent"].ToString().Trim();
                layerUp = Int32.Parse(dtUp.Rows[0]["layer"].ToString().Trim());

                //ma nut ha
                String idDown = dtDown.Rows[0]["idMember"].ToString().Trim();
                String leftCodeDown = dtDown.Rows[0]["leftCode"].ToString().Trim();
                String rigthCodeDown = dtDown.Rows[0]["rigthCode"].ToString().Trim();
                String codeMemberIntroDown = dtDown.Rows[0]["codeMemberIntro"].ToString().Trim();
                String parentCodeDown = dtDown.Rows[0]["CodeMemberParent"].ToString().Trim();
                String codeMemberDown = dtDown.Rows[0]["codeMember"].ToString().Trim();
                layerDown = Int32.Parse(dtDown.Rows[0]["layer"].ToString().Trim());

                sql = "update TreeMemberXu set  layer = '" + layerUp + "', codeMember = '" + codeMemberUp + "',codeMemberIntro = '" + codeMemberIntroUp + "' , upgrade = 'true' where idMember = '" + idDown + "' ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                sql = "update TreeMemberXu set  layer = '" + layerDown + "' ,codeMember = '" + codeMemberDown + "' ,codeMemberIntro = '" + codeMemberIntroDown + "'  where idMember = '" + idUp + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            else
            {

                sql = "update TreeMemberXu set upgrade = 'true' where codeMember = '" + codeMemberUp + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            sqlConn.Close();
            return layerUp;
        }


        public Boolean upgradeAcount(int status, String codeMember)
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String rcodeMember = codeMember.Trim();
            DataTable dt = getMemberFromCode(rcodeMember);
            if (dt.Rows.Count == 0)
                sqlConn.Close();
            return false;
            String sql = "select * from UpgradeAccount where IdMember = '" + rcodeMember + "' ";
            dt = DBUtilEx.getDataFromSQLServer(sqlConn, sql);
            if (dt.Rows.Count == 0)
            {
                sql = "insert into UpgradeAccount (IdMember,Status) values ('" + rcodeMember + "','" + status + "')";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            else
            {
                sql = "Update UpgradeAccount set Status = '" + status + "' where  IdMember = '" + rcodeMember + "'";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
            }
            sqlConn.Close();
            return true;
        }
        public Boolean delUpgrade()
        {
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            String sql = "update TreeMember set upgrade = 'False'";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sql = "delete from UpgradeAccount";
            DBUtilEx.executeNoneQuery(sqlConn, sql);
            sqlConn.Close();
            return true;
        }

        public Boolean addGold(String codeMember, Double gold, Double dealXu, String dateSell)
        {
            Double point = 0;
            String sql = "";
            SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
            DataTable dt = getMemberFromCode(codeMember);
            DateTime dtTime = System.DateTime.Parse(dateSell);

            if (dt.Rows.Count > 0)
            {
                sql = "insert into ProductOrder (MemberId,TotalPrice,TotalDealxu ,OrderDate) values( '" + codeMember + "' , '" + gold + "','" + dealXu + "','" + dtTime + "')";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                return true;
            }
            sqlConn.Close();
            return false;
        }

        public Boolean insertMember(string codeMember, string codeMemberParent)
        {
            try
            {
                String rCodeMember = codeMember.Trim();
                String idMemberParent = getIdFromCode(codeMemberParent).ToString();
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                int layer = 0;
                Boolean free = false;
                dt = getMemberFromCode(codeMemberParent);
                if (dt.Rows.Count > 0)
                {
                    idMemberParent = findParentId(idMemberParent);
                    dt = getMemberFromId(idMemberParent);
                    if (dt.Rows.Count > 0)
                        layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
                }
                else
                {
                    idMemberParent = findParentId(idMemberParent);
                    free = true;
                    dt = getMemberFromId(idMemberParent);
                    if (dt.Rows.Count > 0)
                        layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
                }
                layer++;
                if (idMemberParent == "0")
                    idMemberParent = "";
                String sql = "insert into  TreeMember(CodeMember,codeMemberParent,layer,CodeMemberIntro) values ('" + rCodeMember + "','" + idMemberParent + "' , '" + layer + "','" + codeMemberParent + "' ) ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                updateLeftRigth(rCodeMember, idMemberParent);
                if (free)
                {
                    setFreeMem(idMemberParent, true);
                }
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));

            }
        }

        public Boolean insertMemberXu(string codeMember, string codeMemberParent)
        {
            try
            {
                String rCodeMember = codeMember.Trim();
                String idMemberParent = getIdFromCodeXu(codeMemberParent).ToString();
                SqlConnection sqlConn = DBUtilEx.OpenConnectionSQL(strSql);
                DataTable dt = null;
                int layer = 0;
                Boolean free = false;
                dt = getMemberFromCodeXu(codeMemberParent);
                if (dt.Rows.Count > 0)
                {
                    idMemberParent = findParentIdXu(idMemberParent);
                    dt = getMemberFromIdXu(idMemberParent);
                    if (dt.Rows.Count > 0)
                        layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
                }
                else
                {
                    idMemberParent = findParentIdXu(idMemberParent);
                    free = true;
                    dt = getMemberFromIdXu(idMemberParent);
                    if (dt.Rows.Count > 0)
                        layer = Int32.Parse(dt.Rows[0]["layer"].ToString().Trim());
                }
                layer++;
                if (idMemberParent == "0")
                    idMemberParent = "";
                String sql = "insert into  TreeMemberXu(CodeMember,codeMemberParent,layer,CodeMemberIntro) values ('" + rCodeMember + "','" + idMemberParent + "' , '" + layer + "','" + codeMemberParent + "' ) ";
                DBUtilEx.executeNoneQuery(sqlConn, sql);
                updateLeftRigthXu(rCodeMember, idMemberParent);
                if (free)
                {
                    setFreeMemXu(idMemberParent, true);
                }
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw (new Exception(e.Message));

            }
        }

    }
}
